USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdParentAccountDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdParentAccountDetails
	END
GO

CREATE Procedure dbo.InsUpdParentAccountDetails (
	@PARENTACCOUNTNO		VARCHAR(100)
,	@PARENTACCOUNTGUID		VARCHAR(100)
,	@PARENTACCOUNTNAME		VARCHAR(100)
,	@STREET1				VARCHAR(100) = ''
,	@STREET2				VARCHAR(100) = ''
,	@CITY					VARCHAR(100) = ''
,	@STATE					VARCHAR(100) = ''
,	@ZIP					VARCHAR(100) = ''
,	@PHONE					VARCHAR(100) = ''
,	@FAX					VARCHAR(100) = ''
,	@WEBSITE				VARCHAR(100) = ''
,	@WEBSITEDESC			VARCHAR(100) = ''
,	@NOTEID					VARCHAR(10)	 = ''
,	@NOTES					VARCHAR(max) = ''
,	@ISACTIVE				VARCHAR(10)	 = '1'
,	@USERID					VARCHAR(10)	 = '1'
,	@ADDRESSNO				VARCHAR(10)	 = '0'
,	@ADDRESSGUID			VARCHAR(100) = ''
,	@COMMUNICATIONID		VARCHAR(10)	 = '0'
)
AS
BEGIN

	-- ***************************************************
	-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
	-- START : DO NOT MODIFY THE BELOW CODE
	-- ***************************************************
	DECLARE
		@PARTNERDIVISIONID		VARCHAR(10)
	,	@ACCOUNTGROUP			VARCHAR(10)
	,	@DATAORIGIN				VARCHAR(10)
	,	@ADDRESSTYPE			VARCHAR(10)
	,	@PHONEEXTENSION			VARCHAR(10)
	,	@CELLPHONE				VARCHAR(10)
	,	@NOTETYPE				VARCHAR(10)

	SELECT
		@PARTNERDIVISIONID		= '1'
	,	@ACCOUNTGROUP			= '1'
	,	@DATAORIGIN				= '1'
	,	@ADDRESSTYPE			= '2'
	,	@PHONEEXTENSION			= ''
	,	@CELLPHONE				= ''
	,	@NOTETYPE				= '3'
	-- ***************************************************
	-- END : DO NOT MODIFY THE BELOW CODE
	-- ***************************************************



	DECLARE @C_PARTNERCATEGORY	INT
	DECLARE @C_PARTNERTYPE		INT
	
	SET @C_PARTNERCATEGORY	= 2
	SET @C_PARTNERTYPE		= 1

	IF(@ISACTIVE = 'X')
		SET @ISACTIVE = '1'

	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_PARTNER WHERE PartnerGUID = @PARENTACCOUNTGUID) 
			BEGIN		-- UPDATE
				UPDATE TR_PARTNER
				SET	PartnerName			= @PARENTACCOUNTNAME
				,	PartnerDivision		= @PARTNERDIVISIONID
				,	AccountGroup		= @ACCOUNTGROUP
				,	DataOrigin			= @DATAORIGIN
				,	isActive			= @ISACTIVE
				,	ChangedBy			= @USERID
				,	ChangeDate			= GETDATE()
				,	Website				= @WEBSITE
				,	WebsiteDesc			= @WEBSITEDESC
				WHERE
					PartnerNo			= @PARENTACCOUNTNO
				AND	PartnerGuid			= @PARENTACCOUNTGUID
				AND	PartnerCategory		= @C_PARTNERCATEGORY
				AND	PartnerType			= @C_PARTNERTYPE

				IF ( ISNULL(@ADDRESSGUID,'') = '')
					SELECT @ADDRESSGUID	= CAST(NEWID() AS VARCHAR(100))

			END
			ELSE
			BEGIN		-- INSERT

				-- INSERT ACTIVITY
				SELECT @PARENTACCOUNTNO	= ISNULL(MAX(PartnerNo), 0) +1 FROM TR_PARTNER
				SELECT @ADDRESSGUID		= CAST(NEWID() AS VARCHAR(100))

				-- PARTNER

				INSERT INTO TR_PARTNER (PartnerNo, PartnerGUID, PartnerCategory, PartnerType, PartnerName, 
										PartnerDivision, AccountGroup, DataOrigin, isActive, 
										WebSite, WebSiteDesc,
										CreatedBy, CreateDate, ChangedBy, ChangeDate)
				VALUES(
					@PARENTACCOUNTNO		-- PartnerNo
				,	@PARENTACCOUNTGUID		-- PartnerGUID
				,	@C_PARTNERCATEGORY		-- PartnerCategory
				,	@C_PARTNERTYPE			-- PartnerType
				,	@PARENTACCOUNTNAME		-- PartnerName
				,	@PARTNERDIVISIONID		-- PartnerDivision
				,	@ACCOUNTGROUP			-- AccountGroup
				,	@DATAORIGIN				-- DataOrigin
				,	@ISACTIVE				-- isActive
				,	@WEBSITE				-- WEBSITE
				,	@WEBSITEDESC			-- WebSiteDesc
				,	@USERID					-- CreatedBy
				,	GetDate()				-- CreateDate
				,	@USERID					-- ChangedBy
				,	GetDate()				-- ChangeDate
				)
			END

			exec InsUpdAddress 
				@ADDRESSGUID	= @ADDRESSGUID
			,	@PARTNERGUID	= @PARENTACCOUNTGUID
			,	@ADDRESSNO		= @ADDRESSNO
			,	@STREET1		= @STREET1
			,	@STREET2		= @STREET2
			,	@CITY			= @CITY
			,	@STATE			= @STATE
			,	@ZIP			= @ZIP
			,	@USERID			= @USERID

			exec InsUpdCommunication
				@ADDRESSGUID		= @ADDRESSGUID
			,	@PARTNERGUID		= @PARENTACCOUNTGUID
			,	@COMMUNICATIONID	= @COMMUNICATIONID
			,	@PHONE				= @PHONE
			,	@PHONEEXTENSION		= @PHONEEXTENSION
			,	@FAX				= @FAX
			,	@CELLPHONE			= @CELLPHONE
			,	@USERID				= @USERID

			exec InsUpdNotes
				@NOTEID				= @NOTEID
			,	@NOTESOURCEGUID		= @PARENTACCOUNTGUID
			,	@SOURCEID			= @PARENTACCOUNTNO
			,	@NOTE				= @NOTES
			,	@USERID				= @USERID
			,	@NOTETYPE			= @NOTETYPE

			IF ( CAST(ISNULL(@USERID,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @PARENTACCOUNTGUID
				,	@PARTNERID	= @USERID
				,	@RELATIONSHIPTYPE = 3 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END


			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Parent Account details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Parent Account details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
